
<?
/*/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Created by G.M.Sundar
Date : 15/9/06
functions used for filter
Modified by J.Chandra Kumar
Date : 22 Sep 2006
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////*/
function getFormattedDate($given_date,$format="dd/mm/yyyy",$separator="/")
        {
        // convert the string to date array format
        switch ($format)
                {
                case "dd/mm/yyyy":
                        list ($day, $month, $year) = explode ($separator, $given_date);
                        break;
                case "mm/dd/yyyy":
                        list ($month, $day, $year) = explode ($separator, $given_date);
                        break;
                }
         $date_array=array ($year, $month, $day);
        //convert the date array to the string
        $date_string=implode ('-', $date_array);
        return $date_string;
        }


	
function getTotal($arrays)
        {
include(DOCINCLUDES."global_files/tables.inc");
	global $report_customization;
        $column_array=$report_customization["summary_columns"];
        $grand_total=sumColumnValues($arrays,$column_array);
        $total_arrays[]=$grand_total;
	
	return $total_arrays;
        }
function formatArray(&$arrays,$company_id)
        {
	
        }
	
function issueCalculation(&$arrays,$company_id)
        {
	global $db;
	$cnt=count($arrays);
	for ($i=0; $i<$cnt; $i++)
		{
		$achieved_norms=$arrays[$i]["area"];
		$leather_reqd=$arrays[$i]["leather_reqd"];
		if (($achieved_norms-$leather_reqd) > 0)
			{
			$arrays[$i]["excess"]=$achieved_norms-$leather_reqd;
			$arrays[$i]["saving"]=0;
			}
		else
			{
			$arrays[$i]["saving"]=$leather_reqd-$achieved_norms;
			$arrays[$i]["excess"]=0;
			}
		}
        }
	
function excessValue(&$summary_details,$company_id)
        {
	global $db;
	$cnt=count($summary_details);
	for ($i=0; $i<$cnt; $i++)
		{
		$excess_value=$summary_details[$i]["Excess"];
		$saving_value=$summary_details[$i]["Saving"];
		if ($excess_value > $saving_value)
			{
			$num_excess = $excess_value-$saving_value;	
			$summary_details[$i]["Excess"]=number_format($num_excess,2);
			$summary_details[$i]["Saving"]=number_format(0,2);
			}
		else
			{
			$num_saving=$saving_value - $excess_value;	
			$summary_details[$i]["Saving"]=number_format($num_saving,2);
			$summary_details[$i]["Excess"]=number_format(0,2);
			}
		}
        }
	
function pageValue($page_arrays,$company_id)
{
	global $db;
	$cnt=count($page_arrays);
	for ($i=0; $i<$cnt; $i++)
		{
		$excess_value=$page_arrays[$i]["excess"];
		$saving_value=$page_arrays[$i]["saving"];
		if ($excess_value > $saving_value)
			{
			$num_excess = $excess_value-$saving_value;	
			$page_arrays[$i]["excess"]=number_format($num_excess,2);
			$page_arrays[$i]["saving"]=number_format(0,2);
			}
		else
			{
			$num_saving=$saving_value - $excess_value;	
			$page_arrays[$i]["saving"]=number_format($num_saving,2);
			$page_arrays[$i]["excess"]=number_format(0,2);
			}
		}
        }




		
function grandValue(&$grand_totals_array,$company_id)
{
	global $db,$grand_totals_array;
	$cnt=count($grand_totals_array);
	for ($i=0; $i<$cnt; $i++)
		{
		$excess_value=$grand_totals_array[$i]["Excess"];
		$saving_value=$grand_totals_array[$i]["Saving"];
		if ($excess_value > $saving_value)
			{
			$num_excess = $excess_value-$saving_value;
			$grand_totals_array[$i]["Excess"]=number_format($num_excess,2);
			$grand_totals_array[$i]["Saving"]=number_format(0,2);
			}
		else
			{
			$num_saving=$saving_value - $excess_value;	
			$grand_totals_array[$i]["Saving"]=number_format($num_saving,2);
			$grand_totals_array[$i]["Excess"]=number_format(0,2);
			}
		}
}

function bmnormValue(&$summary_details,$company_id)
        {
			
	global  $db,$sys_design_pattern_specifications_120_350_table,$_mleather_table,$_mcolour_table,$musage_table,$_msubstance_table,$_mleather_type_table,$_jsupplier_table,$sys_design_pattern_120_table,$sal_order_item_specifications_120_350_table,$gen_order_commercial_details_120_350_table,$prd_batch_details,$prd_batch_status,$prd_issue,$material_stock_transaction;
	
	$cnt=count($summary_details);
	
	for ($i=0; $i<$cnt; $i++)
		{
		$leather_name=$summary_details[$i]["Product"];
		$order_no=$summary_details[$i]["Order No"];
		//$order_no=split($order_no,1);
		//$order_no=strstr($order_no,'<');
		print_r($order_no);
		$db->query="select  distinct(sdps.quantity[1]) as  quantity from $sys_design_pattern_specifications_120_350_table  
		  sdps inner join $_mleather_table ml on ml.leather_id=sdps.specifications[2]
		  inner join $_mcolour_table mc on mc.colour_id=sdps.specifications[3] left join $musage_table mu on mu.usage_id=sdps.usage_id
		  left join $_msubstance_table ms on ms.substance_id=sdps.specifications[5]
		  left join $_mleather_type_table mlt on mlt.leather_type_id=sdps.specifications[6]
		  left join $_jsupplier_table js on js.supplier_jid=sdps.specifications[4]
		  inner join $sys_design_pattern_120_table sdp on sdp.pattern_id=sdps.pattern_id
		  join $sal_order_item_specifications_120_350_table sois120 on  sois120.product_id = sdp.product_id
		  inner join  $gen_order_commercial_details_120_350_table gen120 on gen120.order_id = sois120.order_id
		  inner join $prd_batch_details pbd on pbd.order_id= gen120.order_id
		  join $prd_batch_status pbst on pbst.batch_id=pbd.batch_id
		  join $prd_issue pi on pbst.status_id=pi.status_id
		  join $material_stock_transaction mst on pi.issue_id=mst.stock_transaction_id
		  where gen120.partner_reference =".$order_no."and ml.leather_name = '".$leather_name."';";
		
		$db->runQuery();
		
		$bm_norm_array=$db->returnArrays();
		
		$bm_norm = $bm_norm_array[0]["quantity"];
		$summary_details[$i]["BM Norm"]= $bm_norm;
		
		
		}
        }

function addNonPrintableColumns(&$arrays,$company_id)
        {
        }
function getFilters($customization_id)
	{
	global $db,$form,$sys_master_filter,$sys_report_filter_table,$master_column_mapping_table,$master_report,$sys_filter_customization_table,$sys_report_customization_table,$master_report_table,$sys_master_filter_table;
	echo "<form name=\"report_constraint_selection\" method=\"post\" action=\"$PHP_SELF\">";
	
	//query to fetch all filters for this particular report_id if no customization_id is active
	if($customization_id)
		{
		
		
	 	$db->query="select 	
					sfc.filter_id as filter_id,
					smf.filter_name as filter_name,
					smf.filter_table_join,
					smf.form_field_name[1] as form_field_name1,
					smf.form_field_name[2] as form_field_name2,
					sfc.filter_display_name as form_display,
					smf.fetch_condition,
					smf.order_by,
					sfc.filter_order 
					
					from $sys_filter_customization_table sfc join $sys_master_filter_table smf on sfc.filter_id=smf.filter_id where sfc.customization_id=$customization_id order by sfc.filter_order ;";
		$db->runQuery();
		$filter_id_array=$db->returnArrays();
		}
		
		
	
	$c=count($filter_id_array);
	 $db->query="SELECT report_id from $sys_report_customization_table where customization_id =$customization_id ;";
		$db->runQuery();
		$report_id_array=$db->returnArrays();
			
	
	for($i=0;$i<$c;$i++)
		{
		// forming where and order by clause for filter query
		if($filter_id_array[$i]["fetch_condition"])
			$condition= "where ". $filter_id_array[$i]["fetch_condition"]."";
		if($filter_id_array[$i][order_by])
			$order_by="order by ".$filter_id_array[$i]["order_by"]."";
		
		// query to fetch filter
		
		$db->query="select ".$filter_id_array[$i]["form_field_name1"]." from ".$filter_id_array[$i][filter_table_join]." ".$condition." ".$order_by .";";
		$db->runQuery();
		$filter_array=$db->returnArrays();
		
		//query to fetch group column element using master_column_mapping and sys_report_filter
		
		 $db->query="select script_variable_name from $master_column_mapping_table where column_id=(select distinct(group_column) from $sys_report_filter_table where filter_id=".$filter_id_array[$i][filter_id]. " and report_id = ". $report_id_array [0] [report_id] .");";
		$db->runQuery();
		$group_column_array=$db->returnArrays();
		
		//constraint array for grouping
		
		$constraint_array[$filter_id_array[$i][form_display]]=$group_column_array[0]["script_variable_name"];
		// if no of filter greater than 7 then show filter in next row(for our convenience)
	
			$table[0][$filter_id_array[$i]["form_display"]]=$form->generateSelectMenu($filter_id_array[$i]["filter_name"]."[]","",$filter_array,"All",9,true,"","xnarrow");
			unset($condition,$order_by,$filter_array);
		}

		echo "<div align=\"center\">";
		doHorizontalTable($table,"10%");
		echo "</div>";
		

	return $constraint_array;
	
	}
function getGroups($group_name,$group_order,$sort_name,$sort_order,$constraint_array,$no_of_group="")
	{
	//group.inc file replaced 
	global $db,$form;
	$group=generateGroupArray($group_name,$group_order);
	$sort["$sort_name[0]"]=$sort_order[0];

//pass the form values in query string
	$query_string=array("company_id"=>$company_id);
// 	$query_string=array_merge($query_string,generateGroupQueryString($group,$sort));
	$query_string_summary_report=array_merge($query_string,array("report"=>"summary_report"));
	formatConstraintArray($constraint_array);
	
	
$no_of_group_by=($no_of_group)?$no_of_group:5;
$no_of_sort_by=($_SESSION["guser_preferences"]["no_of_sort_by"])?$_SESSION["guser_preferences"]["no_of_sort_by"]:1;
for($g=0;$g<$no_of_group_by;$g++)
	{
	$no_g=$g+1;
	$group_form_formatting["Group ".$no]="text-align:center; width:150px";
	if(!$group_order[0])
		{
		$group_order[0]="ascending";
		}
	$constraint_selection_table[0]["Group ".$no_g]="<label id=\"group\" accesskey=\"G\">";
	$constraint_selection_table[0]["Group ".$no_g].=$form->generateSelectMenu("group_name[$g]",$group_name[$g],$constraint_array,"","",true);
	$constraint_selection_table[0]["Group ".$no_g].="</label>";
	$ascending_order_selected=($group_order[0]=="ascending")?"checked":"";
	$constraint_selection_table[0]["Group ".$no_g].="<br><input type=\"radio\" name=\"group_order[$g]\" value=\"ascending\" $ascending_order_selected >"."Asc";
	$descending_order_selected=($group_order[1]=="descending")?"checked":"";
	$constraint_selection_table[0]["Group ".$no_g].="<input type=\"radio\" name=\"group_order[$g]\" value=\"descending\" $descending_order_selected >"."Desc";
	}
for($s=0;$s<$no_of_sort_by;$s++)
	{
	$no_s=$s+1;
	if(!$sort_order[0])
		{
		$sort_order[0]="ascending";
		}
	$constraint_selection_table[0]["Sort ".$no_s]=$form->generateSelectMenu("sort_name[$s]",$sort_name[$s],$constraint_array,"","","");
	$ascending_order_selected=($sort_order[0]=="ascending")?"checked":"";
	$constraint_selection_table[0]["Sort ".$no_s].="<br><input type=\"radio\" name=\"sort_order[$s]\" value=\"ascending\" $ascending_order_selected >"."Asc";
	$descending_order_selected=($sort_order[1]=="descending")?"checked":"";
	$constraint_selection_table[0]["Sort ".$no_s].="<input type=\"radio\" name=\"sort_order[$s]\" value=\"descending\" $descending_order_selected >"."Desc";
	}
	//include(DOCINCLUDES."global_files/group_form.inc");
        	doHorizontalTable($constraint_selection_table,"","",$group_form_formatting);
	
	
	$group_array[0]["group"]=$group;
	$group_array[0]["sort"]=$sort;
	
	
	return $group_array;
	}
function addQuotes($arrays)
        {
        for($i=0;$i<count($arrays);$i++)
                {
                $new_arrays[$i]="'".$arrays[$i]."'";
                }
        return $new_arrays;
        }
	
function generateConstraintCondition($customization_id)
{
global $db,$master_column_mapping_table, $sys_report_filter_table,$sys_report_customization_table,$sys_master_filter_table;
	 $db->query="SELECT mcm.script_variable_name,smf.filter_name from $master_column_mapping_table mcm join $sys_report_filter_table as srf on mcm.column_id=srf.filter_column join $sys_report_customization_table src on srf.report_id=src.report_id join $sys_master_filter_table smf on smf.filter_id=srf.filter_id  where src.customization_id=$customization_id";
	$db->runQuery();
 	$constraint_array=$db->returnArrays();

	foreach($constraint_array as $key=>$value)
	{
		if ($constraint_condition=="")
			{
			$constraint_condition=($_POST[$value["filter_name"]][0]!="")?"where ". $value["script_variable_name"] ." in  (".implode(',',addQuotes($_POST[$value["filter_name"]])).")":"";
			
			}
		else
			{
			$constraint_condition.=($_POST[$value["filter_name"]][0]!="")?" and ". $value["script_variable_name"] ." in  (".implode(',',addQuotes($_POST[$value["filter_name"]])).")":"";
			
			}
	}
	 return $constraint_condition;
}

function generatetitle($customization_id)
{
global $db,$master_column_mapping_table, $sys_report_filter_table,$sys_report_customization_table,$sys_master_filter_table;
	 $db->query="SELECT mcm.script_variable_name,smf.form_display,smf.filter_name from $master_column_mapping_table mcm join $sys_report_filter_table as srf on mcm.column_id=srf.filter_column join $sys_report_customization_table src on srf.report_id=src.report_id join $sys_master_filter_table smf on smf.filter_id=srf.filter_id  where src.customization_id=$customization_id";
	$db->runQuery();
 	$constraint_array=$db->returnArrays();
	
	foreach($constraint_array as $key=>$value)
	{
		if ($constraint_condition=="")
			{
			$constraint_condition=($_POST[$value["filter_name"]][0]!="")?" - ". $value["form_display"] ." in  (".implode(',',addQuotes($_POST[$value["filter_name"]])).")":"";
			
			}
		else
			{
			$constraint_condition.=($_POST[$value["form_display"]][0]!="")?" and ". $value["form_display"] ." in  (".implode(',',addQuotes($_POST[$value["filter_name"]])).")":"";
			
			}
	}

	 return $constraint_condition;
}

function returnLink($link="",$name="", $query_string_array="", $target="",
$access_key="", $js_event="",$title="")
        {
        $url_string="<a accesskey=\"$access_key\" title=\"$title\" href=\"$link";
        if ($query_string_array)
                {
                $url_string .= "?";
                foreach ($query_string_array as $key => $value)
                        {
                     // check whether the array contains only one item ormore
// than one items.
                        if (count($query_string_array) ==1)   //if only one item
// end the query
                                $url_string .= "$key=$value";
                        else   //otherwise
                                {
                                $url_string .="$key=$value"."&";
                                //  and removes the first value in the array
                                array_shift($query_string_array);
                                }
                        }
                }
        $url_string.="\"";
        $url_string.=($target)?" target=$target" : "";
        $url_string.="><b>$name</b></a>";
        return $url_string;
        }

	
			
?>
